package com.qq.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qq.bean.Friend;
import com.qq.bean.FriendshipVO;
import com.qq.bean.Users;
import com.qq.dao.BaseDao;
import com.qq.dao.FriendshipDao;

public class FriendshipDaoImpl implements FriendshipDao {

	@Override
	public int insertFriendByAccount(String myAccount,String friendAccount) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql1 = "INSERT INTO friend(qq_accounts,add_time,friend_accounts,state) VALUES (?,NOW(),?,1)";
	    String sql2 = "INSERT INTO friend(qq_accounts,add_time,friend_accounts,state) VALUES (?,NOW(),?,1)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql1);
	        ps.setString(1, myAccount);
	        ps.setString(2, friendAccount);
	        ps.executeUpdate();
	        // 第二条
	        ps = con.prepareStatement(sql2);
	        ps.setString(1, friendAccount);
	        ps.setString(2, myAccount);
	        
	        ps.executeUpdate();
	        result = 2; // 两条都成功，返回 2 代表影响两条
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public List<Users> selctByLikeAccount(String account,String me) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Users> users = new ArrayList();
		String sql = "SELECT DISTINCT tb2.* "+
				"FROM friend AS tb1 "+
				"INNER JOIN `users` AS tb2 ON "+
				"tb2.account != tb1.friend_accounts "+
				"WHERE "+
					"tb2.account LIKE ? "+
					"AND tb2.account != ? "+
					"AND tb2.account NOT IN( "+
						"SELECT friend_accounts "+
						"FROM friend "+
						"WHERE (state = 2 OR state = 1) "+
						"AND qq_accounts = ? )";
					

		try {
			con = BaseDao.getCon();
			
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+account+"%");
			ps.setString(2, me);
			ps.setString(3, me);
			res = ps.executeQuery();
			while(res.next()) {
				Users user = new Users();
				user.setUserId(res.getInt("userId"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setRealName(res.getString("realName"));
				user.setPassword(res.getString("password"));
				user.setMoney(res.getDouble("money"));
				user.setAvatar(res.getString("avatar"));
				user.setCreateTime(res.getDate("createTime"));
				users.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		System.out.println(users);
		return users;
	}

	@Override
	public int updateStateByOurAccounts(String myAccount, String friendAccount) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE friend SET state = 2 WHERE (qq_accounts = ? AND friend_accounts = ?) "
				+ "OR (qq_accounts = ? AND friend_accounts = ?)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
	        ps.setString(1, myAccount);
	        ps.setString(2, friendAccount);
	        ps.setString(3, friendAccount);
	        ps.setString(4, myAccount);
	        
	        ps.executeUpdate();
	        result = 2; 
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}
	
	@Override
	public List<FriendshipVO> selectWhoInsertMe(String account) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<FriendshipVO> myFriends = new ArrayList();
		
		String sql = "SELECT qq_accounts,friend_accounts,userId,avatar,nickName,state,add_time FROM friend AS tb1 INNER JOIN users AS tb2 "
				+ "ON tb2.account = tb1.friend_accounts "
				+ "WHERE tb1.qq_accounts = ? "
				+ "AND tb1.friend_accounts != ? "
				+ "AND state = 1 ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, account);
			System.out.println(sql);
			res = ps.executeQuery();
			while(res.next()) {
				FriendshipVO myfriendV = new FriendshipVO();
				myfriendV.setMyaccount(res.getString("qq_accounts"));
				myfriendV.setFriendaccount(res.getString("friend_accounts"));
				myfriendV.setMyFrienduserid(res.getInt("userId"));
				myfriendV.setFirendsavater(res.getString("avatar"));
				myfriendV.setFirendNickname(res.getString("nickName"));
				myfriendV.setOurstate(res.getInt("state"));
				myfriendV.setAddTime(res.getDate("add_time"));
				myFriends.add(myfriendV);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return myFriends;
	}

	@Override
	public Friend selectFriendBy(String account, String me) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Friend friend = null;
		String sql = "SELECT * FROM friend WHERE friend_accounts Like ? AND qq_accounts = ? AND state = 2";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, me);
			System.out.println(sql);
			res = ps.executeQuery();
			if(res.next()) {
				friend = new Friend();
				friend.setFriendId(res.getInt("friendId"));
				friend.setfAccount(res.getString("friend_accounts"));
				friend.setState(res.getInt("state"));
				friend.setMyAccount(res.getString("qq_accounts"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return friend;
	}

	@Override
	public int updateStareBy(String myAccount, String friendAccount) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM friend "
				+ "WHERE (qq_accounts = ? AND friend_accounts = ?) OR (qq_accounts = ? AND friend_accounts = ?)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
	        ps.setString(1, myAccount);
	        ps.setString(2, friendAccount);
	        ps.setString(3, friendAccount);
	        ps.setString(4, myAccount);
	        
	        ps.executeUpdate();
	        result = 2; 
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}
}
